{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "from pandas import Series, DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "apply_demo.csv     demo_duplicate.csv \u001b[31mmovie_metadata.csv\u001b[m\u001b[m usa_flights.csv\r\n",
      "city_weather.csv   iris.csv           sales-funnel.xlsx\r\n"
     ]
    }
   ],
   "source": [
    "!ls ../homework/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df = pd.read_excel('../homework/sales-funnel.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Account</th>\n",
       "      <th>Name</th>\n",
       "      <th>Rep</th>\n",
       "      <th>Manager</th>\n",
       "      <th>Product</th>\n",
       "      <th>Quantity</th>\n",
       "      <th>Price</th>\n",
       "      <th>Status</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>714466</td>\n",
       "      <td>Trantow-Barrows</td>\n",
       "      <td>Craig Booker</td>\n",
       "      <td>Debra Henley</td>\n",
       "      <td>CPU</td>\n",
       "      <td>1</td>\n",
       "      <td>30000</td>\n",
       "      <td>presented</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>714466</td>\n",
       "      <td>Trantow-Barrows</td>\n",
       "      <td>Craig Booker</td>\n",
       "      <td>Debra Henley</td>\n",
       "      <td>Software</td>\n",
       "      <td>1</td>\n",
       "      <td>10000</td>\n",
       "      <td>presented</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>714466</td>\n",
       "      <td>Trantow-Barrows</td>\n",
       "      <td>Craig Booker</td>\n",
       "      <td>Debra Henley</td>\n",
       "      <td>Maintenance</td>\n",
       "      <td>2</td>\n",
       "      <td>5000</td>\n",
       "      <td>pending</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>737550</td>\n",
       "      <td>Fritsch, Russel and Anderson</td>\n",
       "      <td>Craig Booker</td>\n",
       "      <td>Debra Henley</td>\n",
       "      <td>CPU</td>\n",
       "      <td>1</td>\n",
       "      <td>35000</td>\n",
       "      <td>declined</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>146832</td>\n",
       "      <td>Kiehn-Spinka</td>\n",
       "      <td>Daniel Hilton</td>\n",
       "      <td>Debra Henley</td>\n",
       "      <td>CPU</td>\n",
       "      <td>2</td>\n",
       "      <td>65000</td>\n",
       "      <td>won</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>218895</td>\n",
       "      <td>Kulas Inc</td>\n",
       "      <td>Daniel Hilton</td>\n",
       "      <td>Debra Henley</td>\n",
       "      <td>CPU</td>\n",
       "      <td>2</td>\n",
       "      <td>40000</td>\n",
       "      <td>pending</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>218895</td>\n",
       "      <td>Kulas Inc</td>\n",
       "      <td>Daniel Hilton</td>\n",
       "      <td>Debra Henley</td>\n",
       "      <td>Software</td>\n",
       "      <td>1</td>\n",
       "      <td>10000</td>\n",
       "      <td>presented</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>412290</td>\n",
       "      <td>Jerde-Hilpert</td>\n",
       "      <td>John Smith</td>\n",
       "      <td>Debra Henley</td>\n",
       "      <td>Maintenance</td>\n",
       "      <td>2</td>\n",
       "      <td>5000</td>\n",
       "      <td>pending</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>740150</td>\n",
       "      <td>Barton LLC</td>\n",
       "      <td>John Smith</td>\n",
       "      <td>Debra Henley</td>\n",
       "      <td>CPU</td>\n",
       "      <td>1</td>\n",
       "      <td>35000</td>\n",
       "      <td>declined</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>141962</td>\n",
       "      <td>Herman LLC</td>\n",
       "      <td>Cedric Moss</td>\n",
       "      <td>Fred Anderson</td>\n",
       "      <td>CPU</td>\n",
       "      <td>2</td>\n",
       "      <td>65000</td>\n",
       "      <td>won</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>163416</td>\n",
       "      <td>Purdy-Kunde</td>\n",
       "      <td>Cedric Moss</td>\n",
       "      <td>Fred Anderson</td>\n",
       "      <td>CPU</td>\n",
       "      <td>1</td>\n",
       "      <td>30000</td>\n",
       "      <td>presented</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>239344</td>\n",
       "      <td>Stokes LLC</td>\n",
       "      <td>Cedric Moss</td>\n",
       "      <td>Fred Anderson</td>\n",
       "      <td>Maintenance</td>\n",
       "      <td>1</td>\n",
       "      <td>5000</td>\n",
       "      <td>pending</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>239344</td>\n",
       "      <td>Stokes LLC</td>\n",
       "      <td>Cedric Moss</td>\n",
       "      <td>Fred Anderson</td>\n",
       "      <td>Software</td>\n",
       "      <td>1</td>\n",
       "      <td>10000</td>\n",
       "      <td>presented</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>307599</td>\n",
       "      <td>Kassulke, Ondricka and Metz</td>\n",
       "      <td>Wendy Yule</td>\n",
       "      <td>Fred Anderson</td>\n",
       "      <td>Maintenance</td>\n",
       "      <td>3</td>\n",
       "      <td>7000</td>\n",
       "      <td>won</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>688981</td>\n",
       "      <td>Keeling LLC</td>\n",
       "      <td>Wendy Yule</td>\n",
       "      <td>Fred Anderson</td>\n",
       "      <td>CPU</td>\n",
       "      <td>5</td>\n",
       "      <td>100000</td>\n",
       "      <td>won</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>729833</td>\n",
       "      <td>Koepp Ltd</td>\n",
       "      <td>Wendy Yule</td>\n",
       "      <td>Fred Anderson</td>\n",
       "      <td>CPU</td>\n",
       "      <td>2</td>\n",
       "      <td>65000</td>\n",
       "      <td>declined</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>729833</td>\n",
       "      <td>Koepp Ltd</td>\n",
       "      <td>Wendy Yule</td>\n",
       "      <td>Fred Anderson</td>\n",
       "      <td>Monitor</td>\n",
       "      <td>2</td>\n",
       "      <td>5000</td>\n",
       "      <td>presented</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Account                          Name            Rep        Manager  \\\n",
       "0    714466               Trantow-Barrows   Craig Booker   Debra Henley   \n",
       "1    714466               Trantow-Barrows   Craig Booker   Debra Henley   \n",
       "2    714466               Trantow-Barrows   Craig Booker   Debra Henley   \n",
       "3    737550  Fritsch, Russel and Anderson   Craig Booker   Debra Henley   \n",
       "4    146832                  Kiehn-Spinka  Daniel Hilton   Debra Henley   \n",
       "5    218895                     Kulas Inc  Daniel Hilton   Debra Henley   \n",
       "6    218895                     Kulas Inc  Daniel Hilton   Debra Henley   \n",
       "7    412290                 Jerde-Hilpert     John Smith   Debra Henley   \n",
       "8    740150                    Barton LLC     John Smith   Debra Henley   \n",
       "9    141962                    Herman LLC    Cedric Moss  Fred Anderson   \n",
       "10   163416                   Purdy-Kunde    Cedric Moss  Fred Anderson   \n",
       "11   239344                    Stokes LLC    Cedric Moss  Fred Anderson   \n",
       "12   239344                    Stokes LLC    Cedric Moss  Fred Anderson   \n",
       "13   307599   Kassulke, Ondricka and Metz     Wendy Yule  Fred Anderson   \n",
       "14   688981                   Keeling LLC     Wendy Yule  Fred Anderson   \n",
       "15   729833                     Koepp Ltd     Wendy Yule  Fred Anderson   \n",
       "16   729833                     Koepp Ltd     Wendy Yule  Fred Anderson   \n",
       "\n",
       "        Product  Quantity   Price     Status  \n",
       "0           CPU         1   30000  presented  \n",
       "1      Software         1   10000  presented  \n",
       "2   Maintenance         2    5000    pending  \n",
       "3           CPU         1   35000   declined  \n",
       "4           CPU         2   65000        won  \n",
       "5           CPU         2   40000    pending  \n",
       "6      Software         1   10000  presented  \n",
       "7   Maintenance         2    5000    pending  \n",
       "8           CPU         1   35000   declined  \n",
       "9           CPU         2   65000        won  \n",
       "10          CPU         1   30000  presented  \n",
       "11  Maintenance         1    5000    pending  \n",
       "12     Software         1   10000  presented  \n",
       "13  Maintenance         3    7000        won  \n",
       "14          CPU         5  100000        won  \n",
       "15          CPU         2   65000   declined  \n",
       "16      Monitor         2    5000  presented  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th colspan=\"4\" halign=\"left\">Price</th>\n",
       "      <th colspan=\"4\" halign=\"left\">Quantity</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>Product</th>\n",
       "      <th>CPU</th>\n",
       "      <th>Maintenance</th>\n",
       "      <th>Monitor</th>\n",
       "      <th>Software</th>\n",
       "      <th>CPU</th>\n",
       "      <th>Maintenance</th>\n",
       "      <th>Monitor</th>\n",
       "      <th>Software</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Manager</th>\n",
       "      <th>Rep</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Debra Henley</th>\n",
       "      <th>Craig Booker</th>\n",
       "      <td>65000</td>\n",
       "      <td>5000</td>\n",
       "      <td>0</td>\n",
       "      <td>10000</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Daniel Hilton</th>\n",
       "      <td>105000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>10000</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>John Smith</th>\n",
       "      <td>35000</td>\n",
       "      <td>5000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Fred Anderson</th>\n",
       "      <th>Cedric Moss</th>\n",
       "      <td>95000</td>\n",
       "      <td>5000</td>\n",
       "      <td>0</td>\n",
       "      <td>10000</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wendy Yule</th>\n",
       "      <td>165000</td>\n",
       "      <td>7000</td>\n",
       "      <td>5000</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              Price                              Quantity  \\\n",
       "Product                         CPU Maintenance Monitor Software      CPU   \n",
       "Manager       Rep                                                           \n",
       "Debra Henley  Craig Booker    65000        5000       0    10000        2   \n",
       "              Daniel Hilton  105000           0       0    10000        4   \n",
       "              John Smith      35000        5000       0        0        1   \n",
       "Fred Anderson Cedric Moss     95000        5000       0    10000        3   \n",
       "              Wendy Yule     165000        7000    5000        0        7   \n",
       "\n",
       "                                                          \n",
       "Product                     Maintenance Monitor Software  \n",
       "Manager       Rep                                         \n",
       "Debra Henley  Craig Booker            2       0        1  \n",
       "              Daniel Hilton           0       0        1  \n",
       "              John Smith              2       0        0  \n",
       "Fred Anderson Cedric Moss             1       0        1  \n",
       "              Wendy Yule              3       2        0  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(df, index=['Manager','Rep'],values=['Price','Quantity'],columns=['Product'], fill_value=0, aggfunc='sum')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
